﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data;
using System.Web;

    public class M4MEUtil
    {
        //public static bool checkLogin(string u, string p)
        //{
        //    DataTable dt = loadProfile(u); 

        //    return (dt!=null);
        //}

        public static bool isBlackListTel(string tel)
        {
            try
            {
                string sql = "select * from BlackList where tel=" + comm.to_sqltext(tel);
                DataTable dt = DBUtil.SelectTable(sql);
                if (dt.Rows.Count > 0) return true;
            }
            catch { }
            return false;
        }

        public static string trimTelStandard(string u)
        {
            if (u.StartsWith("+84")) u = u.Substring(3);
            //if (u.StartsWith("0")) u = u.Substring(1);
            return u;
        }
        /// <summary>
        /// load profile for username
        /// </summary>
        /// <param name="u"></param>
        /// <returns></returns>
 

        /// <summary>
        /// thực hiện 1 giao dịch cộng/trừ vào tk
        /// </summary>
        /// <param name="u"></param>
        /// <param name="point"></param>
        /// <param name="typeTransaction">1:=,2:+,3:-</param>
        /// <param name="isNeedUpdateM4ME"></param>
        /// <returns></returns>
        public static int updatePointForProfile(string u, string point, int typeTransaction, bool isNeedUpdateM4ME)
        {
            if (typeTransaction == 1)
            {
                string sSql = " Update Members set [point]=" + comm.to_sqlnumber(point) + " where tel like '%" + u + "'";
                return DBUtil.Execute(sSql);
            }
            else if (typeTransaction == 2)
            {
                string sSql = " Update Members set [point]=[point]+" + comm.to_sqlnumber(point) + " where tel like '%" + u + "'";
                return DBUtil.Execute(sSql);
            }
            else if (typeTransaction == 3)
            {
                string sSql = " Update Members set [point]=[point]-" + comm.to_sqlnumber(point) + " where tel like '%" + u + "'";
                return DBUtil.Execute(sSql);
            }
            return -1;
        }
        public static int updatePointForProfile(string u, string point)
        {
            string sSql = " Update Members set [point]=" + comm.to_sqlnumber(point) + " where tel like '%" + u + "'";
            return DBUtil.Execute(sSql);
        }
        public static int getMemberIDByTel(string telToSend)
        {
            DataTable dtMB = DBUtil.SelectTable("select id from Members where tel = " + comm.to_sqltext(telToSend) + "");
            if (dtMB.Rows.Count > 0)
            {
                return int.Parse(dtMB.Rows[0][0].ToString());
            }
            return -1;
        }
       
    
       
 



        /// <summary>
        /// update profile
        /// </summary>
        /// <param name="u"></param>
        /// <param name="arrInfor">dateofbith,fullname,descSelf</param>
        /// <returns></returns>
     
        /// <summary>
        /// Cập nhật thông tin profile vào bảng comp_componentgif cho profile
        /// </summary>
        /// <param name="memberid"></param>
        /// <param name="arr">desSelf</param>
        /// <returns></returns>
        public static int updateProfileCompInfo(string memberid, string[] arr)
        {

            try
            {
                string sDesSelf = arr[0];
                //string sql = "select CG.* from Comp_ComponentGif CG,Members M,Component_Members CM WHERE"
                //    + " CG.id=CM.ComponentGifId "
                //    + " AND M.Id=CM.memberid "
                //    + " AND M.Id=" + memberid
                //    + " AND CG.Type=255 "
                //    + " ";

                //DataTable dt = DBUtil.SelectTable(sql);
                //Check xem nó đã sử dụng comp profile nào không
                //Nếu chưa có cái nào
                //Sử dụng thằng mặc định
                //componentid=0
                //if (dt.Rows.Count == 0)
                //{

                string componentDefaultProfile = M4MEUtil.getIDProfileDefault();
                //Kiểm tra xem nó đã sử dụng comp gif nào chưa
                string sqlc = "select componentId from Component_Members CM,Members M ,Components C where M.Id=CM.memberid AND C.id=CM.ComponentID AND C.Type='Profile'  AND M.Id=" + memberid + " AND CM.active=1";

                object compId = DBUtil.ExecuteScalar(sqlc);

                if (compId == null)
                {
                    compId = componentDefaultProfile;
                    //string sqlin = "INSERT INTO comp_ComponentGif(textContent,[type],memberid,componentid)"
                    //        + " VALUES("
                    //        + "" + comm.to_sql(sDesSelf, "text")
                    //        + ",255"
                    //        + "," + comm.to_sql(memberid, "number")
                    //        + "," + comm.to_sqlnumber(compId.ToString()) + ") SELECT @@IDENTITY ";

                    //object gifid = DBUtil.ExecuteScalar(sqlin);

                    //string sqlcm = "INSERT INTO Component_Members(memberid,componentid,componentGifId,active) VALUES "
                    //           + "(" + comm.to_sqlnumber(memberid.ToString()) + "," + compId + "," + comm.to_sqlnumber(gifid.ToString()) + ",1)";
                    string sqlcm = "INSERT INTO Component_Members(memberid,componentid,active) VALUES "
                                   + "(" + comm.to_sqlnumber(memberid.ToString()) + "," + compId + ",1)";
                    DBUtil.Execute(sqlcm);
                }
                //else 
                //{
                //    string sqlcm = "Update Component_Members SET (memberid,componentid,active) VALUES "
                //                   + "(" + comm.to_sqlnumber(memberid.ToString()) + "," + compId + ",1)";
                //    DBUtil.Execute(sqlcm);
                //}

                //}
                //else
                //{
                //string sqlu = "UPDATE comp_ComponentGif SET textContent=" + comm.to_sql(sDesSelf, "text") + " WHERE id=" + dt.Rows[0]["Id"].ToString() + " AND type=255";// +comm.to_sqlnumber(type) + "";
                //int i = DBUtil.Execute(sqlu);
                //}
            }
            catch (Exception ex) { Util.logM4ME("Exception at updateProfileCompInfo" + ex.ToString()); return -1; }

            return 1;
        }

        /// <summary>
        /// load profile for username
        /// </summary>
        /// <param name="u"></param>
        /// <returns></returns>
        public static DataTable loadGif(string u)
        {
            string sql = "SELECT G.* FROM comp_componentGif G,Components C ,Members M,Component_Members CM"
                + " WHERE (M.tel=" + comm.to_sql(u, "text") + " OR M.tel = '" + u + "')"
                + " AND M.Id=CM.memberid AND C.Id=G.componentid AND C.Id=CM.componentid AND CM.active=1 and G.memberid=M.id";

            DataTable dt = DBUtil.SelectTable(sql);
            if (dt.Rows.Count > 0) return dt;

            return new DataTable();
        }

        /// <summary>
        /// Lấy id của comp gif mà nó đang sử dụng
        /// </summary>
        /// <param name="u">số điện thoại</param>
        /// <param name="type">loại:gift,profile</param>
        /// <returns></returns>
        public static DataTable getActiveComponentByTel(string u, string type)
        {
            string sql = "SELECT C.* FROM Components C ,Members M,Component_Members CM"
                + " WHERE (M.tel=" + comm.to_sql(u, "text") + " OR M.tel = '" + u + "')"
                + " AND C.[type]=" + comm.to_sqltext(type)
                + " AND M.Id=CM.memberid AND C.Id=CM.componentid AND CM.active=1 ";


            DataTable dt = DBUtil.SelectTable(sql);
            if (dt.Rows.Count > 0) return dt;//.Rows[0]["Id"].ToString();
            return new DataTable();
        }
        /// <summary>
        /// update profile
        /// type=1: nickname
        /// type=2: message
        /// </summary>
        /// <param name="u">username</param>
        /// <param name="arrInfor">title,path,type,textContent</param>
        /// <returns></returns>
        public static int updateGif(string u, string[] arrInfor)
        {
            try
            {
                string type = arrInfor[2];
                string textContent = arrInfor[3];
                string path = arrInfor[1];
                string title = arrInfor[0];
                string sql = "select G.*,M.id as memberid from comp_ComponentGif G, Members M,Component_Members CM" +
                    " where M.tel=" + comm.to_sql(u, "text") + " AND G.type=" + comm.to_sqlnumber(type)
                    + " AND M.id=CM.memberid AND G.componentId=CM.componentId AND G.Id=CM.ComponentGifId";

                DataTable dt = DBUtil.SelectTable(sql);
                if (dt.Rows.Count == 0)
                {

                    //Kiểm tra xem nó đã sử dụng comp gif nào chưa
                    string sqlc = "select componentId from Component_Members CM,Members M,Components C where C.id=CM.ComponentID AND C.Type='Gift' AND M.Id=CM.memberid AND M.tel=" + comm.to_sql(u, "text") + " AND CM.active=1";

                    object compId = DBUtil.ExecuteScalar(sqlc);

                    object memberid = DBUtil.ExecuteScalar("Select top 1 id from members where tel=" + comm.to_sql(u, "text") + "");

                    //Nếu chưa có thì cho nó comp mặc định
                    // Sau đó thêm các thuộc tính vào comp mặc định
                    string componentDefaultGift = M4MEUtil.getIDGifDefault();
                    if (compId == null)
                    {

                        string sqlin = "INSERT INTO comp_ComponentGif(title,[path],textContent,[type],memberid,componentid)"
                        + " VALUES(" + comm.to_sql(title, "text")
                        + "," + comm.to_sql(path, "text")
                        + "," + comm.to_sql(textContent, "text")
                        + "," + type + "," + memberid + "," + componentDefaultGift + ") SELECT @@IDENTITY ";

                        object gifid = DBUtil.ExecuteScalar(sqlin);


                        string sqlcm = "INSERT INTO Component_Members(memberid,componentid,componentGifId,active) VALUES "
                            + "(" + comm.to_sqlnumber(memberid.ToString()) + "," + componentDefaultGift + "," + comm.to_sqlnumber(gifid.ToString()) + ",1)";
                        DBUtil.Execute(sqlcm);

                    }
                    //thêm các thuộc tính mới này vào comp nó đang dùng
                    else
                    {
                        string sqlin = " IF NOT EXISTS(SELECT * FROM Comp_ComponentGif WHERE memberid=" + comm.to_sqlnumber(memberid.ToString()) + " AND [Type] =" + comm.to_sqlnumber(type) + " AND Componentid=" + comm.to_sqlnumber(compId.ToString()) + ") "
                            + " BEGIN INSERT INTO comp_ComponentGif(title,[path],textContent,[type],memberid,componentid)"
                        + " VALUES(" + comm.to_sql(title, "text")
                        + "," + comm.to_sql(path, "text")
                        + "," + comm.to_sql(textContent, "text")
                        + "," + comm.to_sqlnumber(type) + "," + memberid + "," + compId.ToString() + ") END; ";

                        //object gifid = DBUtil.ExecuteScalar(sqlin);
                        int nx = DBUtil.Execute(sqlin);
                        if (nx <= 0)
                        {
                            sqlin = "UPDATE Comp_ComponentGif SET Title=" + comm.to_sql(title, "text") + ",[Path]=" + comm.to_sql(path, "text") + ", textContent=" + comm.to_sql(textContent, "text")
                                + " WHERE componentid=" + comm.to_sqlnumber(compId.ToString()) + " AND [Type]=" + comm.to_sqlnumber(type) + " AND Memberid=" + comm.to_sqlnumber(memberid.ToString()) + "";
                            nx = DBUtil.Execute(sqlin);
                        }
                        //string sqlcm = "INSERT INTO Component_Members(memberid,componentid,componentGifId,active) VALUES "
                        //    + "(" + comm.to_sqlnumber(memberid.ToString()) + "," + componentDefaultGift + "," + comm.to_sqlnumber(gifid.ToString()) + ",1)";
                        //DBUtil.Execute(sqlcm);

                    }

                }
                else
                {
                    //Nếu là nick name hoac loi nhan hoac ban ghi am
                    if (type == "1" || type == "2" || type == "3")
                    {
                        string sqlu = "UPDATE comp_ComponentGif SET title=" + comm.to_sql(title, "text") + ",textContent=" + comm.to_sql(textContent, "text") + " WHERE id=" + dt.Rows[0]["Id"].ToString() + " AND type=" + comm.to_sqlnumber(type) + "";
                        int i = DBUtil.Execute(sqlu);

                    }
                    else
                    {
                        string sqlu = "UPDATE Component_Gift SET title=" + comm.to_sql(title, "text") + ", path=" + comm.to_sql(path, "text") + " WHERE id=" + dt.Rows[0]["Id"].ToString() + " AND type=" + comm.to_sqlnumber(type) + "";
                        int i = DBUtil.Execute(sqlu);
                    }
                }
            }
            catch (Exception ex) { return -1; }
            return 1;
        }

        public static string getIDGifDefault()
        {
            string sqlu = "select id from Components where IsNull(Point,0)=0 and type='Gift'";
            DataTable dt = DBUtil.SelectTable(sqlu);
            return dt.Rows.Count > 0 ? dt.Rows[0][0].ToString() : "0";
        }
        public static string getIDProfileDefault()
        {
            string sqlu = "select id from Components where IsNull(Point,0)=0 and type='Profile'";
            DataTable dt = DBUtil.SelectTable(sqlu);
            return dt.Rows.Count > 0 ? dt.Rows[0][0].ToString() : "0";
        }
        /// <summary>
        /// kích hoạt lời ghi âm
        /// </summary>
        /// <returns></returns>
        //public static bool updateActiveGifRecord(string sTel) 
        //{
        //    string sql = " SELECT CM.componentgifid,CM.memberid FROM comp_componentGif G ,Members M,Component_Members CM "
        //                + " WHERE M.Tel="+comm.to_sqltext(sTel)+" AND G.id=CM.componentGifId AND M.Id=CM.memberid AND G.type=3 ";
        //    DataTable dt = DBUtil.SelectTable(sql);
        //    if (dt.Rows.Count == 0) return false;

        //    string sqlup = "UPDATE component_members SET active=1 WHERE componentgifid=" + comm.to_sqlnumber(dt.Rows[0]["componentgifid"].ToString()) + " AND memberid=" + comm.to_sqlnumber(dt.Rows[0]["memberid"].ToString()) + " ";

        //    return (DBUtil.Execute(sqlup)>0);

        //    return false;
        //}
        /// <summary>
        /// Lấy component của sdt
        /// </summary>
        /// <param name="tel"></param>
        /// <returns></returns>
        public static DataTable getComponentGif(string tel)
        {
            //string sql = " SELECT G.*,M.desSelf,C.Type as ComponentType,C.Path as CompPath FROM comp_componentGif G ,Members M,Component_Members CM,Components C "
            //           + " WHERE M.Tel = '" + tel + "' AND G.componentid=CM.componentId AND M.Id=CM.memberid "
            //           + " AND G.Id=CM.ComponentGifId AND CM.active=1 AND C.Id = G.componentid AND C.Id =CM.componentid ";

            string sql = " SELECT G.*,C.Type as ComponentType,C.Path as CompFilePath FROM comp_componentGif G"
                        + " INNER JOIN Component_Members CM ON G.componentid=CM.componentId  "
                        + " INNER JOIN Members M ON CM.memberid = M.Id "
                        + " INNER JOIN Components C ON CM.componentid=C.Id "
                       + " WHERE M.Tel = '" + tel + "' AND G.MemberId=M.Id "
                       + " AND CM.active=1 ";

            DataTable dt = DBUtil.SelectTable(sql);
            return dt;
        }
        /// <summary>
        /// Lấy all shop của social
        /// </summary>
        /// <param name="tel"></param>
        /// <returns></returns>
        public static DataTable getAllShop()
        {
            string sql = " SELECT * FROM comp_componentGif G";
            DataTable dt = DBUtil.SelectTable(sql);
            return dt;
        }
        /// <summary>
        /// lấy điểm hiện tại của thành viên
        /// </summary>
        /// <returns></returns>
        public static int getCurrentPoint()
        {
            DataTable dt = DBUtil.SelectTable("select Isnull(Point,0) from Members where id=" + comm.GetMemberID() + "");
            if (dt.Rows.Count > 0) return int.Parse(dt.Rows[0][0].ToString());
            return -1;
        }
        /// <summary>
        /// Thêm comp vào danh sách comp hiện có
        /// 1: thành công
        /// -1: thất bại (không đủ điểm trong tài khoản)
        /// 0: Đã có trong danh sách rồi
        /// -2: Không tồn tại
        /// -3: Chưa có tài khoản trên MXH
        /// seri: thẻ
        /// nocard: Đã hết hàng trong kho
        /// </summary>
        /// <param name="tel"></param>
        /// <param name="componentid"></param>
        /// <returns>
        /// string[]{type,value}
        /// //type=1: qua tang va profile, value=diem mua
        /// //type=2: the dien thoai, value=diem mua,value2=seri the
        /// </returns>
        public static string[] buyItem(string tel, string componentid, string point)
        {

            string sql = "select * from Components where id=" + comm.to_sqlnumber(componentid);

            DataTable dt = DBUtil.SelectTable(sql);

            if (dt.Rows.Count == 0) return new string[]{"1","-2"};

            string sPointNeed = dt.Rows[0]["point"].ToString();
            string sType = dt.Rows[0]["type"].ToString();

            if (sType.ToLower() == "vhty") return new string[] { "1", "-2" };

            //10000 < 2000
            if (int.Parse(point) >= (int.Parse(sPointNeed)))
            {


                if (sType.ToLower() == "card")//nếu là mua thẻ điện thoại
                {
                    string channel = dt.Rows[0]["cardchannel"].ToString();
                    string cardtype = dt.Rows[0]["cardtype"].ToString();
                    string sqlcard = "select top 1 * from Components_card where cardmoney=" + comm.to_sqlnumber(cardtype) + " and channel=" + comm.to_sqltext(channel) + " and isnull(status,0)=0";
                    DataTable dtCard = DBUtil.SelectTable(sqlcard);
                    if (dtCard.Rows.Count > 0) 
                    {
                        string seri = dtCard.Rows[0]["seri"].ToString();
                        //đánh dấu thằng này đã được sử dụng
                        DBUtil.Execute("update components_card set status=1,dateused=getdate(),pointtobuy=" + comm.to_sqlnumber(sPointNeed) + ",telused=" + comm.to_sqltext(tel) + " where seri=" + comm.to_sqltext(seri));
                        return new string[] { "2", sPointNeed, seri };
                    }
                    //Hết thẻ trong kho
                    return new string[] { "2", sPointNeed, "nocard" };
                }


                string sqlt = "SELECT * FROM Members WHERE Tel=" + comm.to_sqltext(tel) + " ";
                DataTable dtMem = DBUtil.SelectTable(sqlt);
                if (dtMem.Rows.Count == 0) return  new string[]{"1","-3"};

                string memberid = dtMem.Rows[0]["Id"].ToString();

                string sqlse = "select * from Component_Members WHERE componentid=" + comm.to_sqlnumber(componentid) + " AND memberid=" + comm.to_sqlnumber(memberid.ToString());
                DataTable dtCount = DBUtil.SelectTable(sqlse);
                if (dtCount.Rows.Count > 0) return new string[]{"1","0"};;


                //string sqls = "select id from comp_ComponentGif where componentid="+comm.to_sqlnumber(componentid)+" where typeComponent is not null ";
                //DataTable dtGif = DBUtil.SelectTable(sqls);

                //foreach (DataRow dr in dtGif.Rows)
                //{
                //    string sqlins = "INSERT INTO Component_Members(componentid,memberid,componentGifId)VALUES("
                //        + comm.to_sqlnumber(componentid)+","
                //        + comm.to_sqlnumber(memberid) + ","
                //        + comm.to_sqlnumber(dr["Id"].ToString()) + ""
                //    +")";

                //    DBUtil.Execute(sqlins);
                //}
                string sqlins2 = "INSERT INTO Component_Members(componentid,memberid)VALUES("
                        + comm.to_sqlnumber(componentid) + ","
                        + comm.to_sqlnumber(memberid) + ""
                    + ")";
                DBUtil.Execute(sqlins2);


                //return 1;
                return  new string[]{"1",sPointNeed};
            }

            else
            {
                return  new string[]{"1","-1"};
            }


            return  new string[]{"1","0"};
        }
        /// <summary>      
        /// </summary>
        /// <param name="tel">Tel</param>
        /// <param name="componentid">componentid</param>
        /// <param name="sType">Gift,Profile</param>
        /// <returns>
        /// -1: Nó không có comp này
        /// -2: Comp này cần phải cập nhật lời nhắn quà tặng trước
        /// 1: thành công
        /// </returns>
        public static int changeComponentToUse(string tel, string componentid, string sType)
        {
            bool isProfile = (sType.ToLower() == "profile");
            object memberid = DBUtil.ExecuteScalar("SELECT id FROM Members WHERE Tel=" + comm.to_sqltext(tel) + " ");

            //check xem là có đúng nó có comp này không
            //if (componentid != "0")
            //{
            string sqlcheck = "SELECT * FROM Component_Members WHERE memberid=" + comm.to_sqlnumber(memberid.ToString())
                + " AND ComponentId=" + comm.to_sqlnumber(componentid) + " AND (active is null or active =0)";
            DataTable dtCheck = DBUtil.SelectTable(sqlcheck);
            if (dtCheck.Rows.Count == 0) return -1;
            //}
            //Lấy danh sách thuộc tính nó định nghĩa(gửi tới,lời nhắn, record) của comp nó đang sử dụng

            //string sql = "SELECT * FROM Component_Members WHERE 1=1"
            //    + " AND memberid=" + comm.to_sqlnumber(memberid.ToString())
            //    + " AND active=1 AND componentgifid IN(SELECT Id FROM comp_ComponentGif WHERE 1=1 AND [type] is not null and typeComponent is null)";


            string sql = "SELECT CG.* FROM Comp_ComponentGif CG,Component_Members CM  WHERE 1=1"
                + " AND CM.memberid=" + comm.to_sqlnumber(memberid.ToString())
                + " AND CM.active=1 AND CG.[type] is not null and CG.typeComponent is null ";


            string sqlCurrent = "SELECT top 1 ComponentId FROM Component_Members CM,Components C WHERE CM.memberid=" + memberid.ToString()
                + " AND CM.active=1 AND CM.ComponentId = C.Id AND C.Type=" + comm.to_sqltext(sType) + " ";

            object compCurrentId = DBUtil.ExecuteScalar(sqlCurrent);

            //Lấy các componetgifid nó tạo (nickname,message,record)            
            string sGifIdList = "";
            string sProGifIdList = "";
            DataTable dt = DBUtil.SelectTable(sql);
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    if (!isProfile && dt.Rows[i]["type"].ToString() != "255")
                    {
                        if (sGifIdList == "") sGifIdList += dt.Rows[i]["id"].ToString();
                        else sGifIdList += "," + dt.Rows[i]["id"].ToString();
                    }
                    else if (isProfile && dt.Rows[i]["type"].ToString() == "255")
                    {
                        if (sProGifIdList == "") sProGifIdList += dt.Rows[i]["id"].ToString();
                        else sProGifIdList += "," + dt.Rows[i]["id"].ToString();
                    }
                }
            }


            //Lay cac thuoc tinh cua thang comp nay

            //string sql2 = "SELECT CG.* FROM Comp_ComponentGif CG,Components C WHERE CG.componentid=" + comm.to_sqlnumber(componentid) 
            //    + " AND CG.typeComponent is not null AND CG.ComponentId = C.Id AND C.Type="+comm.to_sqltext(sType)+" ";

            //DataTable dtGif = DBUtil.SelectTable(sql2);

            ////Nếu nó la Gif va chưa tạo lời nhắn và comp này chưa có thuộc tính nào thì trả về là Comp này cần cập nhật quà tặng trước
            //if (!isProfile)
            //{
            //    if (dtGif.Rows.Count == 0 && sGifIdList == "") return -2;
            //}
            ////Nếu nó la Profile va chưa tạo desSelf và comp này chưa có thuộc tính nào thì trả về là Comp này cần cập nhật desSelf
            //else 
            //{
            //    if (dtGif.Rows.Count == 0 && sProGifIdList == "") return -3;
            //}


            //foreach (DataRow dr in dtGif.Rows)
            //{
            //    string sqlins = "INSERT INTO Component_Members(componentid,componentgifid,memberid,active)VALUES("
            //        + comm.to_sqlnumber(componentid) + ","
            //        + comm.to_sqlnumber(dr["Id"].ToString()) + ","
            //        + comm.to_sqlnumber(memberid.ToString()) + ","
            //        + "1"
            //        + ") ";

            //    DBUtil.Execute(sqlins);
            //}

            if (compCurrentId != null)
            {
                //Bỏ trạng thái đang sử dụng của thằng comp nó đang dùng                                
                //string sqlup = "DELETE FROM Component_Members WHERE componentid=" + comm.to_sqlnumber(compCurrentId.ToString()) + " AND memberid=" + comm.to_sqlnumber(memberid.ToString()) + " AND active = 1";

                string sqlup = "UPDATE Component_Members SET active=0 WHERE componentid=" + comm.to_sqlnumber(compCurrentId.ToString()) + " AND memberid=" + comm.to_sqlnumber(memberid.ToString()) + " AND active = 1";
                DBUtil.Execute(sqlup);

                string sqlup2 = "UPDATE Component_Members SET active=1 WHERE componentid=" + comm.to_sqlnumber(componentid) + " AND memberid=" + comm.to_sqlnumber(memberid.ToString()) + "";
                DBUtil.Execute(sqlup2);
            }

            //Chen cac thuoc tinh no dang su dung
            //Neu la gif
            if (sGifIdList != "")
            {
                //string sqlins = "INSERT INTO Component_Members(componentid,componentgifid,memberid,active) "
                //    + "SELECT " + comm.to_sqlnumber(componentid) + ",Id," + comm.to_sqlnumber(memberid.ToString()) + ",1 FROM comp_ComponentGif WHERE Id IN(" + sGifIdList + ")"
                //    + " ";

                //DBUtil.Execute(sqlins);
                string sqlups = "UPDATE Comp_ComponentGif SET componentid=" + comm.to_sqlnumber(componentid) + " WHERE Id IN (" + sGifIdList + ") AND memberid=" + comm.to_sqlnumber(memberid.ToString()) + "";
                DBUtil.Execute(sqlups);
            }

            //Nếu là profile

            //cập nhật trạng thái profile về chưa duyệt để đổi item profile mới khi được duyệt lại

            if (isProfile)
            {
                string sqlups = "UPDATE Members SET isapproved=null WHERE Id=" + comm.to_sqlnumber(memberid.ToString()) + " ";
                DBUtil.Execute(sqlups);
            }


            if (sProGifIdList != "")//Hiện tại thằng này không còn cần thiết nữa vì profile đã đưa vào bảng Members chứ không dùng ở comp nữa
            {
                //string sqlins = "INSERT INTO Component_Members(componentid,componentgifid,memberid,active) "
                //    + "SELECT " + comm.to_sqlnumber(componentid) + ",Id," + comm.to_sqlnumber(memberid.ToString()) + ",1 FROM comp_ComponentGif WHERE Id IN(" + sProGifIdList + ")"
                //    + " ";
                //DBUtil.Execute(sqlins);

                string sqlups = "UPDATE Comp_ComponentGif SET componentid=" + comm.to_sqlnumber(componentid) + " WHERE Id IN (" + sProGifIdList + ")";

                DBUtil.Execute(sqlups);
            }


            return 1;
        }
        public static string getLevelName(string sLevel)
        {
            string sResult = "";

            if (sLevel == "1") sResult = "Pro";
            else if (sLevel == "2") sResult = "Dân chơi";
            else if (sLevel == "3") sResult = "Đại gia";
            else sResult = "Dân thường";
            return sResult;
        }

        public static string getPathPreview(string sPath)
        {
            return sPath.Replace(".swf", "-Preview.swf");
        }

        /// <summary>
        /// Lay danh sach profile chua duyet de duyet
        /// </summary>
        /// <returns></returns>
        public static DataTable getDescSelftForFilter()
        {
            try
            {
                //string sql = "SELECT * FROM Members WHERE isapproved is null ";
                string sql = "SELECT M.*,C.id as componentid,C.Path as CompFilePath FROM Members M,Component_Members CM, Components C"
                             + " WHERE M.isapproved is null AND M.Id= CM.Memberid"
                            + " AND C.Id = CM.componentid AND CM.active=1 AND C.Type='Profile'";
                DataTable dt = DBUtil.SelectTable(sql);
                string sTelList = "";
                foreach (DataRow dr in dt.Rows)
                {
                    if (sTelList == "") sTelList = dr["Id"].ToString();
                    else sTelList += "," + dr["Id"].ToString(); ;
                }
                //Đánh dấu trạng thái đã lấy về logic chờ duyệt
                if (sTelList != "")
                {
                    string sqlup = " update Members set isapproved=1 where Id in(" + sTelList + ")";
                    DBUtil.Execute(sqlup);
                }
                return dt;
            }
            catch { }
            return new DataTable();
        }
        /// <summary>
        /// cap nhat trang thai duyet desself
        /// </summary>
        /// <param name="isapproved"></param>
        /// <param name="arrTel"></param>
        public static void approveDesSelf(bool isapproved, string[] arrTel)
        {
            string sql = "";
            try
            {
                int iIsapproved = (isapproved == true) ? 1 : 0;

                string sTelList = "";
                foreach (string sTel in arrTel)
                {
                    if (sTelList == "") sTelList += "'" + Util.getTelToSend(sTel) + "'";
                    else sTelList += ",'" + Util.getTelToSend(sTel) + "'";
                }
                if (sTelList == "") sTelList = "0";
                sql = "update Members set isapproved=" + comm.to_sqlnumber(iIsapproved.ToString()) + " where tel in(" + sTelList + ")";
                DBUtil.Execute(sql);
            }
            catch (Exception ex) { Util.logM4ME("Exception at approveDesSelf(" + sql + "):" + ex.ToString()); }
        }
      
        public static DataTable getTopSong(int limit, string subjectid)
        {
            string sql = "select top " + limit + " * from song where isapproved=1"
                + " AND id in(select songid from songsubject where subjectid=" + comm.to_sqlnumber(subjectid) + ")"
                + " order by times desc";
            DataTable dt = DBUtil.SelectTable(sql);
            return dt;
        }
        public static DataTable loadCataLogShop()
        {
            DataTable dt = DBUtil.SelectTable("select * from ComponentType where isview=1");
            return dt;
        }
        public static DataTable loadShopHomeByType(string typeid, string numberRec)
        {
            DataTable dt = DBUtil.SelectTable("select top " + numberRec + " *  from Components where type=" + comm.to_sqltext(typeid) + "");
            return dt;
        }
        public static DataTable loadShopHomeByType(string typeid, string skeyword, string numberRec)
        {
            DataTable dt = DBUtil.SelectTable("select top " + numberRec + " *  from Components where title like N'%" + comm.to_sqltextforlike(skeyword) + "%'");
            return dt;
        }

        public static DataTable loadShopByType(string typeid, string skeyword, string numberRec)
        {
            DataTable dt = DBUtil.SelectTable("select top " + numberRec + " *  from Components where title like N'%" + comm.to_sqltextforlike(skeyword) + "%' and type=" + comm.to_sqltext(typeid) + "");
            return dt;
        }
        public static DataTable loadShopByTypeAndCatId(string typeid, string sCatId, string numberRec)
        {
            if (string.IsNullOrEmpty(sCatId)) sCatId = "-1";
            DataTable dt = DBUtil.SelectTable("select top " + numberRec + " *  from Components where type=" + comm.to_sqltext(typeid) + " and Isnull(typeid,0)=" + comm.to_sqlnumber(sCatId) + "");
            return dt;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="lastdatelogin">lastdatelogin</param>
        /// <returns>image,status</returns>
        public static string[] getOnlineStatus(string lastdatelogin)
        {
            string onlineimage = "";
            string onlinestatus = "";
            try
            {
                DateTime lstlg = DateTime.Parse(lastdatelogin);
                int o = DateTime.Now.Subtract(lstlg).Minutes;
                if (o <= 5)
                {
                    onlineimage = "<img src=\"/images/myprofile/yahoo.png\" />";
                    onlinestatus = "<b>Đang online</b>";
                    return new string[] { onlineimage, onlinestatus };
                }
            }
            catch { }
            return new string[] { "<img src=\"/images/offline.jpeg\" />", "Đang offline" };
        }

        public static DataTable GetAllComponentGifByMemberID(string memberid, int top)
        {
            string sTop = "top " + top.ToString() + " ";
            if (top == -1) sTop = "";
            string sqlItem = "SELECT " + sTop + " C.*,CM.active "
                        + " FROM Components C,Component_Members CM"
                        + " WHERE CM.memberid=" + comm.to_sqlnumber(memberid.ToString())
                        + " AND C.Id=CM.ComponentId AND C.Type='gift'";

            DataTable dtItems = DBUtil.SelectTable(sqlItem);
            return dtItems;
        }
        public static DataTable GetAllComponentProfileByMemberID(string memberid, int top)
        {
            string sTop = " top " + top.ToString() + " ";
            if (top == -1) sTop = "";

            string sqlItem = "SELECT " + sTop + " C.*,CM.active "
                        + " FROM Components C,Component_Members CM"
                        + " WHERE CM.memberid=" + comm.to_sqlnumber(memberid.ToString())
                        + " AND C.Id=CM.ComponentId AND C.Type='profile'";

            DataTable dtItems = DBUtil.SelectTable(sqlItem);
            return dtItems;
        }
        public static DataTable GetAllStoreByMemberID(string memberid, int top)
        {
            string sTop = " top " + top.ToString() + " ";
            if (top == -1) sTop = "";

            string sqlItem = "SELECT " + sTop + " C.*,CM.Point"
                        + " FROM Bidding_Item C,Bidding_Winner CM"
                        + " WHERE CM.memberid=" + comm.to_sqlnumber(memberid.ToString())
                        + " AND C.songid=CM.biddingid";

            DataTable dtItems = DBUtil.SelectTable(sqlItem);
            return dtItems;
        }
        public static DataTable GetAllStoreByMemberTel(string Tel, int top)
        {
            string sTop = " top " + top.ToString() + " ";
            if (top == -1) sTop = "";

            Tel = Util.getTelToCompare(Tel);

            //string sqlItem = "SELECT " + sTop + " C.*,CM.Point"
            //            + " FROM Bidding_Item C,Bidding_Winner CM"
            //            + " WHERE CM.Tel  like '%" + comm.to_sqltextforlike(Tel)
            //            + "' AND C.songid=CM.biddingid";
             
             string sqlItem = "select " + sTop + " item.id,item.title,win.point ,left(win.dateend,8) dateend,item.songid,sum(fund.point) as totalFund,sum(fund.pointWinner) as totalPoint,fund.ClanId,fund.ClanTitle,item.imgthumbpath " +
                           "  from Bidding_Item item" +
                           "  join Bidding_Winner win on win.biddingid =item.songid" +
                           "  left join Bidding_Fund fund on fund.biddingid =item.songid" +
                           "  where win.Tel like '%" + Tel + "' and isnull(fund.statusFinish,0)=0 " +
                           "  and win.dateend>=" + DateTime.Now.ToString("yyyyMMddHHmmss") +
                           "  group by fund.ClanId,fund.ClanTitle,item.imgthumbpath,songid,item.id,item.title,win.point,left(win.dateend,8)";

            DataTable dtItems = DBUtil.SelectTable(sqlItem);
            return dtItems;
        }

        public static int isTrangColor(string p)
        {
            if (p.ToLower().IndexOf("/home/home.aspx") != -1) return 0;

            //trang cá nhân
            if (p.ToLower().IndexOf("/member/myprofile.aspx") != -1) return 1;

            //private show
            if (p.ToLower().IndexOf("/privateshow/") != -1) return 11;

            //trang thẻ
            if (p.ToLower().IndexOf("/card/") != -1) return 10;

            //trang news
            if (p.ToLower().IndexOf("/news/") != -1) return 2;

            //trang kho nhạc
            if (p.ToLower().IndexOf("/musicstore/") != -1) return 3;

            //trang clan
            if (p.ToLower().IndexOf("/clan/") != -1) return 4;

            //trang mshop
            if (p.ToLower().IndexOf("/home/mshop") != -1 || p.ToLower().IndexOf("/games/gamedetail") != -1) return 5;

            //trang mstore
            if (p.ToLower().IndexOf("/daugia/") != -1) return 6;

            //trang trang member
            if (p.ToLower().IndexOf("/member/") != -1) return 7;


            //trang faq
            if (p.ToLower().IndexOf("/member/faq.aspx") != -1) return 9;

            //trang member
            if (p.ToLower().IndexOf("/forum/") != -1) return 8;
 
            return -1;
        }

        public static DataTable getAdvs(int place)
        {
            string sql = "SELECT * FROM ITemAdvs where (startdate<=getdate() or startdate is null) and (enddate >=getdate() or enddate is null) and place=" + comm.to_sqlnumber(place.ToString()) + " order by id";

            DataTable dt = DBUtil.SelectTable(sql);
            return dt;
        }
    }